package com.winning.sjzc.sjypz.utils;

import com.sun.rowset.CachedRowSetImpl;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;

/**
 * @ClassName SqlExecutorUtils
 * @Description TODO
 * @Author WJX
 * @Date 2019/8/16 9:02
 **/
public class SqlExecutorUtils {

    /**
     * 调用sql
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public static ResultSet excuteQuery(Connection conn,String sql,Object[] params){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0, n = params.length; i < n; i++) {
                Object param = params[i];
                if (param == null) {
                    ps.setNull(i + 1, java.sql.Types.VARCHAR);
                } else {
                    ps.setObject(i + 1, param);
                }
            }
            ps.execute();
            rs = ps.getResultSet();
            if (rs == null){
                // 如果结果集为null,则说明为update，insert之类的语句
                return null;
            }
        }catch (Exception e){
            return null;
        }
        return rs;
    }

    /**
     * 调用存储
     * @param conn
     * @param sql
     * @param params
     * @param databasetype
     * @return
     */
    public static ResultSet excutePro(Connection conn,String sql,Object[] params,String databasetype){
        ResultSet rs = null;
        CallableStatement proc = null;
        try {
            proc = conn.prepareCall(sql);
            for (int i = 0, n = params.length; i < n; i++) {
                Object param = params[i];
                if (param == null) {
                    proc.setNull(i + 1, java.sql.Types.VARCHAR);
                } else {
                    proc.setObject(i + 1, param);
                }
            }
            if(StringUtils.equalsIgnoreCase("oracle",databasetype)){
                proc.registerOutParameter(params.length + 1,
                        oracle.jdbc.OracleTypes.CURSOR);
                proc.execute();
                rs = (ResultSet) proc.getObject(params.length + 1);
            }else if(StringUtils.equalsIgnoreCase("sqlserver",databasetype)){
                rs = proc.executeQuery();
            }
            CachedRowSetImpl cacheRS = new CachedRowSetImpl();
            cacheRS.populate(rs);
            return cacheRS;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

}
